//
//  DBManager.m
//  uCal
//
//  Created by apple on 11-3-30.
//  Copyright 2011 __MyCompanyName__. All rights reserved.
//

#import "CacheManager.h"
#import "sqlite3.h"
#import "Cache.h"

#define DataFile @"data_cache.db"

static sqlite3 *database;

@implementation CacheManager

+ (NSString*)databasePath{
	NSString *pathname = [NSHomeDirectory() stringByAppendingPathComponent: @"tmp"];
	return [pathname stringByAppendingPathComponent:DataFile];
}

/*
 ******************************************
 *Cache Table                          *
 ******************************************
 */
#define CACHE_ALL_INFO 0
+ (NSArray *)queryAllFromCache{
	
	if (sqlite3_open([[self databasePath] UTF8String],&database) != SQLITE_OK) {
		sqlite3_close(database);
		printf("open database faild at queryAllFromCache()!\n");
		return nil;
	}
	
	NSMutableArray *cacheArray = [[NSMutableArray alloc]init];
	
	NSString *countSQL = @"SELECT ckey, cobject, csize, createdate FROM wcity_cache";
	sqlite3_stmt *statement;
	if (sqlite3_prepare_v2(database, [countSQL UTF8String], -1, &statement, nil) == SQLITE_OK) {
		
		while (sqlite3_step(statement)==SQLITE_ROW) {
			
			Cache *cache = [[Cache alloc] init];
			
			cache.ckey = [NSString stringWithUTF8String: (const char *)sqlite3_column_text(statement, 0)];
			
			cache.cobject = [NSString stringWithUTF8String: (const char *)sqlite3_column_text(statement, 1)];
			
			cache.csize = sqlite3_column_int(statement, 2);
			
			cache.createdate = [NSString stringWithUTF8String: (const char *)sqlite3_column_text(statement, 3)];
			
			[cacheArray addObject: cache];
			
#if CACHE_ALL_INFO
			printf("ckey=%s, cobject=%s, csize=%d, createdate=%s\n", 
				  [cache.ckey UTF8String],
				  [cache.cobject UTF8String],
				  cache.csize,
				   [cache.createdate UTF8String]);
#endif
			
			[cache release];
		}
		sqlite3_finalize(statement);
	}
	sqlite3_close(database);
	
	return [cacheArray autorelease];
}

+ (Cache *)queryCacheWithCacheKey:(NSString *)cacheKey{
	
	if (sqlite3_open([[self databasePath] UTF8String],&database) != SQLITE_OK) {
		sqlite3_close(database);
		printf("open database faild at queryCacheWithCacheKey() !\n");
		return nil;
	}

	NSString *countSQL = [NSString stringWithFormat:@"SELECT ckey, cobject, csize, createdate FROM wcity_cache WHERE ckey = '%@'",cacheKey];
	sqlite3_stmt *statement;
	if (sqlite3_prepare_v2(database, [countSQL UTF8String], -1, &statement, nil) == SQLITE_OK) {
		
		Cache *cache = [[Cache alloc]init];
		
		while (sqlite3_step(statement)==SQLITE_ROW) {
			
			cache.ckey = [NSString stringWithUTF8String: (const char *)sqlite3_column_text(statement, 0)];
			
			cache.cobject = [NSString stringWithUTF8String: (const char *)sqlite3_column_text(statement, 1)];
			
			cache.csize = sqlite3_column_int(statement, 2);
			
			cache.createdate = [NSString stringWithUTF8String: (const char *)sqlite3_column_text(statement, 3)];
			
		}
		
		sqlite3_finalize(statement);
		
		return [cache autorelease];

	}
	sqlite3_close(database);
	return nil;
}

+ (NSInteger)queryCacheSize{
	if (sqlite3_open([[self databasePath] UTF8String],&database) != SQLITE_OK) {
		sqlite3_close(database);
		printf("open database faild at queryCacheSize()!\n");
		return 0;
	}
	NSString *countSQL = @"SELECT SUM(csize) FROM wcity_cache";
	sqlite3_stmt *statement;
	if (sqlite3_prepare_v2(database, [countSQL UTF8String], -1, &statement, nil) == SQLITE_OK) {
		while (sqlite3_step(statement)==SQLITE_ROW) {
			int i = sqlite3_column_int(statement,0);
			sqlite3_finalize(statement);
			sqlite3_close(database);
			return i;
		}
	}
	sqlite3_close(database);
	return 0;
}

#define CALENDAR_INSERT_SQL_INFO 0
+ (void)saveCache:(Cache *)cache{
	
	if (cache==nil) {
		printf("cache is NULL!\n");
		return;
	}
	
	if (sqlite3_open([[CacheManager databasePath] UTF8String],&database) != SQLITE_OK) {
		sqlite3_close(database);
		printf("open database faild at saveCache()!\n");
		return;
	}
	
	NSString *createSQL = @"CREATE TABLE IF NOT EXISTS wcity_cache(cacheid INTEGER PRIMARY KEY, ckey TEXT, cobject TEXT, csize INTEGER, createdate TEXT)";
	char *erroMsg;
	@try {
		if (sqlite3_exec(database, [createSQL UTF8String], NULL, NULL, &erroMsg) != SQLITE_OK) {
			sqlite3_close(database);
			printf("create table wcity_cache faild\n");
			printf("the error is %s\n",erroMsg);
			return;
		}
	}
	@catch (NSException *ex) {
		printf("create table wcity_cache exception\n");
		printf("the exception is %s\n",[ex.name UTF8String]);
		sqlite3_close(database);
		return;
	}
	
	NSString *insertSql = @"INSERT INTO wcity_cache(ckey, cobject, csize, createdate) ";
	insertSql = [insertSql stringByAppendingString: @" VALUES('%@', '%@', %d, datetime('now','localtime'))"];
	NSString *insertUser = [NSString stringWithFormat: insertSql,
							cache.ckey,
							cache.cobject,
							cache.csize];
	
#if CALENDAR_INSERT_SQL_INFO
	NSLog(@"%@", insertUser);
#endif

	@try {
		if (sqlite3_exec (database, [insertUser UTF8String], NULL, NULL, &erroMsg) != SQLITE_OK)
		{
			printf("Error inserting tables: %s\n", erroMsg);
		}
	}
	@catch (NSException * ex) {
		printf("exception inserting tables: %s\n", [ex.name UTF8String]);
	}
	@finally {
		sqlite3_close(database);
	}
	
}

+ (void)clearCache:(NSInteger)numbers{
	
	if (sqlite3_open([[CacheManager databasePath] UTF8String],&database) != SQLITE_OK) {
		sqlite3_close(database);
		printf("open database faild at clearCache()!\n");
		return;
	}
	
	char *erroMsg;
	NSString *deleteSql = @"DELETE FROM wcity_cache";
	if (numbers!=0) {
		deleteSql = [NSString stringWithFormat: @"DELETE FROM (SELECT * FROM wcity_cache limit %d)", numbers];
	}
	
	
#if CALENDAR_DELETE_SQL_INFO
	NSLog(@"%@", deleteSql);
#endif
	
	@try {
		if (sqlite3_exec (database, [deleteSql UTF8String], NULL, NULL, &erroMsg) != SQLITE_OK)
		{
			printf("Error deleting tables: %s\n", erroMsg);
		}
	}
	@catch (NSException * ex) {
		printf("exception deleting tables: %s\n", [ex.name UTF8String]);
	}
	@finally {
		sqlite3_close(database);
	}
	
}


@end
